﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

namespace POSPlus.PrintManager
{
	public class SpecialPrintInfo : DatabaseView, IComparable<SpecialPrintInfo>
	{
		public String Description { get; private set; }
		public int Vendor { get; private set; }
		public String CertCode { get; private set; }
		public double AvgDailyMovement { get; private set; }
		public short Pack { get; private set; }
		public String Size { get; private set; }
		public double UnitNP { get; private set; }
		public String Uom { get; private set; }
		public int Hosted { get; private set; }
		public decimal NormalPrice { get; private set; }
		public short PriceMethod { get; set; }
		public short Quantity { get; private set; }
		public decimal GroupPrice { get; private set; }
		public String Upc { get; private set; }
		public int LocAisle { get; private set; }
		public int LocBlock { get; private set; }
		public int LocSeq { get; private set; }
		public decimal SpecialPrice { get; private set; }
		public short SpecialPriceMethod { get; private set; }
		public double SpecialQuantity { get; private set; }
		public decimal SpecialGroupPrice { get; private set; }
		public String WhereToBuy { get; private set; }
		public String PalletCode { get; private set; }
		public DateTime StartDate { get; private set; }
		public DateTime EndDate { get; private set; }
		public double UnitSP { get; private set; }
		public String Rehang { get; private set; }
		public String BatchNo { get; private set; }
		public DateTime DealEndDate { get; private set; }
		public String JobName { get; private set; }

		public SpecialPrintInfo(string connectionString) : base(connectionString) { }

		public SpecialPrintInfo(String connectionString, String description, int vendor, String certcode, 
			double avgDailyMovement,short pack, string size, double unitnp, string uom, int hosted, decimal normalprice, 
			short pricemethod, short quantity, decimal groupprice, string upc, int locaisle, int locblock, int locseq, 
			decimal specialprice,	short specialpricemethod, double specialquantity, decimal specialgroupprice, string wheretobuy, 
			string palletcode, DateTime startdate, DateTime enddate, double unitsp, string rehang, string batchno, 
			DateTime dealenddate, String jobName) 
			: base(connectionString)
		{
			Description = description;
			Vendor = vendor;
			CertCode = certcode;
			AvgDailyMovement = avgDailyMovement;
			Pack = pack;
			Size = size;
			UnitNP = unitnp;
			Uom = uom;
			Hosted = hosted;
			NormalPrice = normalprice;
			PriceMethod = pricemethod;
			Quantity = quantity;
			GroupPrice = groupprice;
			Upc = upc;
			LocAisle = locaisle;
			LocBlock = LocBlock;
			LocSeq = LocSeq;
			SpecialPrice = specialprice;
			SpecialPriceMethod = specialpricemethod;
			SpecialQuantity = SpecialQuantity;
			SpecialGroupPrice = specialgroupprice;
			WhereToBuy = wheretobuy;
			PalletCode = palletcode;
			StartDate = startdate;
			EndDate = enddate;
			UnitSP = unitsp;
			Rehang = rehang;
			BatchNo = batchno;
			DealEndDate = dealenddate;
			JobName = jobName;
		}

		public SpecialPrintInfo(String connectionString, String upc, int locAisle, int locBlock, int locSeq)
			: base(connectionString)
		{
			using (SqlConnection connection = new SqlConnection(ConnectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT * FROM POSplus_SpecialPrintInfo WHERE Upc = @Upc AND LocAisle = @LocAisle " +
					"AND LocBlock = @LocBlock AND LocSeq = @LocSeq", connection))
				{
					command.Parameters.AddWithValue("@Upc", upc);
					command.Parameters.AddWithValue("@LocAisle", locAisle);
					command.Parameters.AddWithValue("@LocBlock", locBlock);
					command.Parameters.AddWithValue("@LocSeq", locSeq);
					using (SqlDataReader reader = command.ExecuteReader())
					{
						if (reader.Read())
						{
							Description = FromDB<String>(reader["Description"]);
							Vendor = FromDB<int>(reader["Vendor"]);
							CertCode = FromDB<String>(reader["CertCode"]);
							AvgDailyMovement = FromDB<double>(reader["AvgDailyMovement"]);
							Pack = FromDB<short>(reader["Pack"]);
							Size = FromDB<String>(reader["Size"]);
							UnitNP = FromDB<double>(reader["Unit_NP"]);
							Uom = FromDB<String>(reader["Uom"]);
							Hosted = FromDB<int>(reader["Hosted"]);
							NormalPrice = FromDB<decimal>(reader["Normal_Price"]);
							PriceMethod = FromDB<short>(reader["PriceMethod"]);
							Quantity = FromDB<short>(reader["Quantity"]);
							GroupPrice = FromDB<decimal>(reader["GroupPrice"]);
							Upc = FromDB<String>(reader["Upc"]);
							LocAisle = FromDB<int>(reader["LocAisle"]);
							LocBlock = FromDB<int>(reader["LocBlock"]);
							LocSeq = FromDB<int>(reader["LocSeq"]);
							SpecialPrice = FromDB<decimal>(reader["SpecialPrice"]);
							SpecialPriceMethod = FromDB<short>(reader["SpecialPriceMethod"]);
							SpecialQuantity = FromDB<short>(reader["SpecialQuantity"]);
							SpecialGroupPrice = FromDB<decimal>(reader["SpecialGroupPrice"]);
							WhereToBuy = FromDB<String>(reader["WhereToBuy"]);
							PalletCode = FromDB<string>(reader["PalletCode"]);
							StartDate = FromDB<DateTime>(reader["Start_Date"]);
							EndDate = FromDB<DateTime>(reader["End_Date"]);
							UnitSP = FromDB<double>(reader["Unit_SP"]);
							Rehang = FromDB<String>(reader["Rehang"]);
							BatchNo = FromDB<String>(reader["BatchNo"]);
							DealEndDate = FromDB<DateTime>(reader["DealEndDate"]);
							JobName = FromDB<String>(reader["JobName"]);
						}
					}
				}
				connection.Close();
			}
		}

		public static List<SpecialPrintInfo> GetAll(String connectionString, String jobName)
		{
			List<SpecialPrintInfo> result = new List<SpecialPrintInfo>();
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT * FROM POSplus_SpecialPrintInfo WHERE JobName = @JobName", connection))
				{
					command.Parameters.AddWithValue("@JobName", jobName);
					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							SpecialPrintInfo info = new SpecialPrintInfo(
								connectionString,
								FromDB<String>(reader["Description"]),
								FromDB<int>(reader["Vendor"]),
								FromDB<string>(reader["Cert_Code"]),
								FromDB<double>(reader["AvgDailyMovement"]),
								FromDB<short>(reader["Pack"]),
								FromDB<String>(reader["Size"]),
								FromDB<double>(reader["Unit_NP"]),
								FromDB<String>(reader["Uom"]),
								FromDB<int>(reader["Hosted"]),
								FromDB<decimal>(reader["Normal_Price"]),
								FromDB<short>(reader["PriceMethod"]),
								FromDB<short>(reader["Quantity"]),
								FromDB<decimal>(reader["GroupPrice"]),
								FromDB<String>(reader["Upc"]),
								FromDB<int>(reader["LocAisle"]),
								FromDB<int>(reader["LocBlock"]),
								FromDB<int>(reader["LocSeq"]),
								FromDB<decimal>(reader["Special_Price"]),
								FromDB<short>(reader["SpecialPriceMethod"]),
								FromDB<double>(reader["SpecialQuantity"]),
								FromDB<decimal>(reader["SpecialGroupPrice"]),
								FromDB<String>(reader["WhereToBuy"]),
								FromDB<string>(reader["PalletCode"]),
								FromDB<DateTime>(reader["Start_Date"]),
								FromDB<DateTime>(reader["End_Date"]),
								FromDB<double>(reader["Unit_SP"]),
								FromDB<String>(reader["Rehang"]),
								FromDB<String>(reader["BatchNo"]),
								FromDB<DateTime>(reader["DealEndDate"]),
								FromDB<String>(reader["JobName"])
								);
							result.Add(info);
						}
					}
				}
				connection.Close();
			}
			return result;
		}

		public int CompareTo(SpecialPrintInfo other)
		{
			if (LocAisle != default(int) && other.LocAisle == default(int))
				return -1;

			if (LocAisle == default(int) && other.LocAisle != default(int))
				return 1;

			string otherRehang = (other.Rehang == null) ? "" : other.Rehang;
			string rehang = (Rehang == null) ? "" : Rehang;

			if (!string.IsNullOrEmpty(rehang) && string.IsNullOrEmpty(otherRehang))
				return 1;

			if (string.IsNullOrEmpty(rehang) && !string.IsNullOrEmpty(otherRehang))
				return -1;

			if (!string.IsNullOrEmpty(rehang) && !string.IsNullOrEmpty(otherRehang) && rehang != otherRehang)
			{
				return rehang.CompareTo(otherRehang);
			}

			if (LocAisle > other.LocAisle)
				return 1;

			if (LocAisle < other.LocAisle)
				return -1;

			if (LocBlock > other.LocBlock)
				return 1;

			if (LocBlock < other.LocBlock)
				return -1;

			if (LocSeq > other.LocSeq)
				return 1;

			if (LocSeq < other.LocSeq)
				return -1;

			return Upc.CompareTo(other.Upc);
		}

		internal static IEnumerable<SpecialPrintInfo> GetFuture(string connectionString, string jobName)
		{
			List<SpecialPrintInfo> info = new List<SpecialPrintInfo>();
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT * FROM POSplus_FutureSpecialPrintInfo WHERE JobName = @JobName", connection))
				{
					command.Parameters.AddWithValue("@JobName", jobName);
					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							SpecialPrintInfo oneInfo = new SpecialPrintInfo(connectionString);
							oneInfo.Upc = FromDB<string>(reader["Upc"]);
							oneInfo.Description = FromDB<string>(reader["Description"]);
							oneInfo.CertCode = FromDB<string>(reader["Cert_Code"]);
							oneInfo.PriceMethod = FromDB<short>(reader["PriceMethod"]);
							oneInfo.GroupPrice = FromDB<decimal>(reader["GroupPrice"]);
							oneInfo.NormalPrice = FromDB<decimal>(reader["Normal_Price"]);
							oneInfo.Quantity = FromDB<short>(reader["Quantity"]);
							oneInfo.Pack = FromDB<short>(reader["Pack"]);
							oneInfo.Size = FromDB<string>(reader["Size"]);
							oneInfo.Vendor = FromDB<int>(reader["Vendor"]);
							oneInfo.Hosted = FromDB<int>(reader["Hosted"]);
							oneInfo.LocAisle = FromDB<int>(reader["LocAisle"]);
							oneInfo.LocBlock = FromDB<int>(reader["LocBlock"]);
							oneInfo.LocSeq = FromDB<int>(reader["LocSeq"]);
							oneInfo.UnitNP = FromDB<double>(reader["Unit_NP"]);
							oneInfo.Uom = FromDB<string>(reader["Uom"]);
							oneInfo.AvgDailyMovement = FromDB<double>(reader["AvgDailyMovement"]);
							oneInfo.BatchNo = FromDB<string>(reader["BatchNo"]);
							oneInfo.SpecialPriceMethod = FromDB<short>(reader["SpecialPriceMethod"]);
							oneInfo.SpecialPrice = FromDB<decimal>(reader["Special_Price"]);
							oneInfo.SpecialGroupPrice = FromDB<decimal>(reader["SpecialGroupPrice"]);
							oneInfo.SpecialQuantity = FromDB<double>(reader["SpecialQuantity"]);
							oneInfo.UnitSP = FromDB<double>(reader["Unit_SP"]);
							oneInfo.Rehang = FromDB<string>(reader["Rehang"]);
							oneInfo.WhereToBuy = FromDB<string>(reader["WhereToBuy"]);
							oneInfo.StartDate = FromDB<DateTime>(reader["Start_Date"]);
							oneInfo.EndDate = FromDB<DateTime>(reader["End_Date"]);
							oneInfo.DealEndDate = FromDB<DateTime>(reader["DealEndDate"]);
							oneInfo.PalletCode = FromDB<String>(reader["PalletCode"]);
							info.Add(oneInfo);
						}
						reader.Close();
					}
				}
				connection.Close();
			}
			return info;
		}
		internal static IEnumerable<SpecialPrintInfo> Get(string connectionString, string jobName)
		{
			List<SpecialPrintInfo> info = new List<SpecialPrintInfo>();
			using (SqlConnection connection = new SqlConnection(connectionString))
			{
				connection.Open();
				using (SqlCommand command = new SqlCommand("SELECT * FROM POSplus_SpecialPrintInfo WHERE JobName = @JobName", connection))
				{
					command.Parameters.AddWithValue("@JobName", jobName);
					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							SpecialPrintInfo oneInfo = new SpecialPrintInfo(connectionString);
							oneInfo.Upc = FromDB<string>(reader["Upc"]);
							oneInfo.Description = FromDB<string>(reader["Description"]);
							oneInfo.CertCode = FromDB<string>(reader["Cert_Code"]);
							oneInfo.PriceMethod = FromDB<short>(reader["PriceMethod"]);
							oneInfo.GroupPrice = FromDB<decimal>(reader["GroupPrice"]);
							oneInfo.NormalPrice = FromDB<decimal>(reader["Normal_Price"]);
							oneInfo.Quantity = FromDB<short>(reader["Quantity"]);
							oneInfo.Pack = FromDB<short>(reader["Pack"]);
							oneInfo.Size = FromDB<string>(reader["Size"]);
							oneInfo.Vendor = FromDB<int>(reader["Vendor"]);
							oneInfo.Hosted = FromDB<int>(reader["Hosted"]);
							oneInfo.LocAisle = FromDB<int>(reader["LocAisle"]);
							oneInfo.LocBlock = FromDB<int>(reader["LocBlock"]);
							oneInfo.LocSeq = FromDB<int>(reader["LocSeq"]);
							oneInfo.UnitNP = FromDB<double>(reader["Unit_NP"]);
							oneInfo.Uom = FromDB<string>(reader["Uom"]);
							oneInfo.AvgDailyMovement = FromDB<double>(reader["AvgDailyMovement"]);
							oneInfo.BatchNo = FromDB<string>(reader["BatchNo"]);
							oneInfo.SpecialPriceMethod = FromDB<short>(reader["SpecialPriceMethod"]);
							oneInfo.SpecialPrice = FromDB<decimal>(reader["Special_Price"]);
							oneInfo.SpecialGroupPrice = FromDB<decimal>(reader["SpecialGroupPrice"]);
							oneInfo.SpecialQuantity = FromDB<double>(reader["SpecialQuantity"]);
							oneInfo.UnitSP = FromDB<double>(reader["Unit_SP"]);
							oneInfo.Rehang = FromDB<string>(reader["Rehang"]);
							oneInfo.WhereToBuy = FromDB<string>(reader["WhereToBuy"]);
							oneInfo.StartDate = FromDB<DateTime>(reader["Start_Date"]);
							oneInfo.EndDate = FromDB<DateTime>(reader["End_Date"]);
							oneInfo.DealEndDate = FromDB<DateTime>(reader["DealEndDate"]);
							oneInfo.PalletCode = FromDB<String>(reader["PalletCode"]);
							info.Add(oneInfo);
						}
						reader.Close();
					}
				}
				connection.Close();
			}
			return info;
		}
	}
}
